Chapter 10 — Data Modelling
Code Reference File — Copy and paste as needed

========================================
10.1.1 Transactional Query
========================================
from datetime import datetime
last_year = datetime.now().year - 1
sales['Date'] = pd.to_datetime(sales['Date'])
australia_cities = ['Melbourne', 'Sydney', 'Brisbane', 'Canberra']
australia_last_year = sales[
    (sales['Retailer City'].isin(australia_cities)) &
    (sales['Date'].dt.year == last_year)
]
print(australia_last_year['Retailer City'].drop_duplicates())

========================================
10.1.2 Analytical Query
========================================
sales_agg = australia_last_year.groupby('Retailer City').agg(
    Number_of_Sales=('Retailer City', 'count'),
    Total_Revenue=('Revenue', 'sum')
)
print(sales_agg)

========================================
10.3 Filter by Retailer Type
========================================
filtered_sales = sales[sales['Retailer Type'] == 'Sports Store']
print(filtered_sales.head())

========================================
10.4 Preview Tables
========================================
print(sales[['Revenue', 'Quantity Sold']].head())
print(countries[['Country', 'City']].head())
print(product[['Product Line', 'Product Type', 'Product Name']].head())
